/**
 * 
 */
package org.compiere.process;

import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.logging.Level;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.apps.DialogParameters;
import org.compiere.model.I_BPM_Parameters;
import org.compiere.model.MBPMForm;
import org.compiere.model.MBPMFormLine;
import org.compiere.model.MBPMParameters;
import org.compiere.model.MColumn;
import org.compiere.model.MFactAcct;
import org.compiere.model.MQuery;
import org.compiere.model.MTable;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

/**
 * @author V.Sokolov
 *
 */
public class BPMFormDataImport extends SvrProcess {
	
	/* Current context		*/
	private Properties m_ctx;
	/* */
	private String path = "";
	/* */
	private int BPM_Form_ID = 0;
	/* */
	private int AD_Column_ID = 0;
	private int AD_Table_ID = 0;
	MColumn column = null;
	MTable table = null;
	/* */
	int ID = 10000;

	/* 
	 */
	@Override
	protected void prepare() {
		
		log.info(" Import data form BP ");
		m_ctx = Env.getCtx();
		
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)	{
			String name = para[i].getParameterName();
			if (name == null); 
				//
			else if (name.toLowerCase().endsWith("directory"))
				path = (String)para[i].getParameter();
			else if (name.compareTo("BPM_Form_ID") == 0 && para[i].getParameter() != null)
				BPM_Form_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else if (name.compareTo("AD_Column_ID") == 0 && para[i].getParameter() != null)
				AD_Column_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else if (name.compareTo("AD_Table_ID") == 0 && para[i].getParameter() != null)
				AD_Table_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else
			{
				log.log(Level.INFO, "Unknown Parameter: " + name);
			}
		}
		
		if(path.isEmpty())
			path = Util.localFilePath;
		else
			path = path + System.getProperty("file.separator");
		
		column = new MColumn(m_ctx, AD_Column_ID, get_TrxName());
		table = new MTable(m_ctx, AD_Table_ID, get_TrxName());
	}

	/* 
	 */
	@Override
	protected String doIt() throws Exception {
		
		if(column.getColumnName().isEmpty())
			return "Column is empty";
		
		File file = new File(path.toString());
		
		// 
		Workbook tableBook = null;
		Sheet sheet = null;
		 try {
			 tableBook = Workbook.getWorkbook(file);
			 sheet = tableBook.getSheet(0);
		} catch (Exception e) {
		  	ADialog.error(999, null, "Error tableBook. " + e.getMessage());
		   	return "Error tableBook.";
		}

		ExcelCell cellStart =  Util.getCellStart(tableBook,">>");
		ExcelCell cellEnd =  Util.getCellStart(tableBook,"<<");
		
		MBPMFormLine[] formLine = MBPMForm.getLines(m_ctx, BPM_Form_ID, get_TrxName());
		if(formLine.length > cellEnd.getR() 
				|| formLine.length < cellEnd.getR())
			throw new AdempiereException("Неверная структура файла");
				
		int col = cellStart.getC();
		col++;
		int rowStart = cellStart.getR();
		Cell cell = null;
		int i = rowStart;
		int IDNext = getID();
		for(MBPMFormLine l: formLine){
			
			cell = sheet.getCell(col, i);
			String[] val = cell.getContents().trim().split(",");
			if(val.length >= 1 && !val[0].isEmpty()){
				for(String v: val){
					// insert
					MBPMParameters param = new MBPMParameters(m_ctx, 0, get_TrxName());
					param.setBPM_Parameters_ID(IDNext);
					param.setAD_Table_ID(MBPMFormLine.Table_ID);
					param.setRecord_ID(l.getBPM_FormLine_ID());
					param.setColumnName(column.getColumnName());
					param.setValue1(v);
					param.setAD_Column_ID(MColumn.getColumn_ID(table.getTableName(), column.getColumnName()));
					param.setOperation(DialogParameters.OPERATORS[MQuery.EQUAL_INDEX].getValue());
					param.setAD_Table2_ID(MFactAcct.Table_ID);
					if(!MBPMParameters.isValue(m_ctx, param.getAD_Table_ID(), param.getRecord_ID(), param.getColumnName(), param.getValue1(), param.getAD_Table2_ID(), get_TrxName())
							&& !v.isEmpty()){
						param.saveEx();
						IDNext++;
					}
				}
			}
			i++;
		}
		
		tableBook.close();
		
		return Msg.translate(m_ctx, "Success");
	}
	
	private int getID(){
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int result = ID;

		try
		{
			pstmt = DB.prepareStatement("select max("+I_BPM_Parameters.COLUMNNAME_BPM_Parameters_ID+") \n " +
					"from "+I_BPM_Parameters.Table_Name +" \n" +
					"where "+I_BPM_Parameters.COLUMNNAME_BPM_Parameters_ID+" < "+1000000, get_TrxName());
			rs = pstmt.executeQuery();	
			if(rs.next ()){
				if(rs.getInt(1) != 0)
					return rs.getInt(1)+100;
			}
		}
		catch (SQLException e)
		{
			log.log(Level.INFO, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		
		return result++;
	}

}
